﻿using System;
using System.Data;
using System.Data.OracleClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DBUtility;
using Model;
using Model.DutyMainPage;

/*
创建日期：2009-03-04
编写人：张广  
功能说明: 值班主页
*/

namespace OracleDAL.DutyMainPage
{
    /// <summary>
    /// 值班主页
    /// </summary>
    public class WatchMain
    {
        /// <summary>
        /// 故障数据
        /// </summary>
        public DataSet GetMalfunction(string netWorkYYbm, string str, string duty_gzxsglsz,string duty_gzxsglszif,string paixuStr)
        {
            StringBuilder strSql = new StringBuilder();


            strSql.Append("select rownum,t.* from  ");
            strSql.Append("(select * from allnet_t_fault ");
            strSql.Append("where networkid in (" + netWorkYYbm + ") ");
            strSql.Append("and " + str + " ");

            if (duty_gzxsglszif == "N")
            {
                //不显示大于某个时长的一些故障
                strSql.Append("and times<" + int.Parse(duty_gzxsglsz) + "  ");

            }
            strSql.Append("order by " + paixuStr + ") t  ");


            return OracleHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 查询今天值班人员
        /// </summary>
        /// <param name="unitisn">单位</param>
        /// <returns></returns>
        public OracleDataReader GetTodayWatch(string unitisn)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select username from duty_arrange where unitisn='" + unitisn + "' and dutyday=to_date('" + System.DateTime.Now.ToShortDateString() + "','YYYY-MM-DD')  ");

            return OracleHelper.ExecuteReader(strSql.ToString());
        }

        /// <summary>
        /// 查询明天值班人员
        /// </summary>
        /// <param name="unitisn">单位</param>
        /// <returns></returns>
        public OracleDataReader GetTomorrowWatch(string unitisn)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select username from duty_arrange where unitisn='" + unitisn + "' and dutyday=to_date('" + System.DateTime.Now.AddDays(1).ToShortDateString() + "','YYYY-MM-DD')  ");

            return OracleHelper.ExecuteReader(strSql.ToString());
        }

        /// <summary>
        /// 查询后台故障处理模块状态
        /// </summary>
        /// <param name="faultSerialno">故障编号</param>
        /// <returns></returns>
        public DataSet GetNewFault(string faultSerialno)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select rownum,t.* from (select * from com_mgr_adp_status where entityname in (" + faultSerialno + ") order by ALIVETIME) t ");
            
            return OracleHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 查询网系长时间未发现故障列表
        /// </summary>
        /// <returns></returns>
        public DataSet GetNewFaultTime()
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select y.yymc,f.* from allnet_fault_cur_status f,com_yybm y  ");
            strSql.Append("where f.isalarm=1 and sysdate-f.fault_updatetime>f.threshold/1440 and f.networkid=y.yybm and  ");
            strSql.Append("mod((sysdate-f.fault_updatetime)*1440,f.threshold)<5 order by f.networkid ");
            
            return OracleHelper.Query(strSql.ToString());
        }

        
        /// <summary>
        /// 查询后台故障处理模块状态
        /// </summary>
        public DataSet GetFaultStatus()
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select y.yymc,f.* from allnet_fault_cur_status f,com_yybm y  ");
            strSql.Append("where f.isalarm=1 and sysdate-f.fault_updatetime>f.threshold/1440 and f.networkid=y.yybm and  ");
            strSql.Append("mod((sysdate-f.fault_updatetime)*1440,f.threshold)<5 order by f.networkid  ");


            return OracleHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 查询后台状态
        /// </summary>
        public DataSet GetAdpStatus()
        {
            StringBuilder strSql = new StringBuilder();
            string DictionaryTable = "tn_t_tabledictionarynew"; //字典表
            string adp_statusTable = "com_mgr_adp_status"; //后台状态表

            strSql.Append("select * from " + DictionaryTable + " where tablename=\'" + adp_statusTable + "\'  ");

            return OracleHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 查询后台状态
        /// </summary>
        /// <param name="enfield">字段</param>
        /// <param name="nodeid">节点id</param>
        /// <returns></returns>
        public DataSet GetAdpStatus(string enfield, string nodeid)
        {
            StringBuilder strSql = new StringBuilder();

            string adp_statusTable = "com_mgr_adp_status"; //后台状态表

            strSql.Append("select " + enfield + " from " + adp_statusTable + " where nodeid like \'%" + nodeid + "%\' and isalarm=\'Y\'  ");

            return OracleHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 查询门限
        /// </summary>
        public DataSet GetFirstMenXian()
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select * from allnet_faulttype where enable <> 'N'  ");

            return OracleHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 历史故障数据
        /// </summary>
        /// <param name="strNode">节点</param>
        /// <param name="starttime">开始时间</param>
        /// <param name="endtime">结束时间</param>
        /// <param name="sswx">网系id</param>
        /// <param name="gzlx">类型</param>
        /// <param name="gzlxtj"></param>
        /// <param name="ssqy"></param>
        /// <param name="zrjd"></param>
        /// <param name="cxsj"></param>
        /// <param name="qztj"></param>
        /// <param name="qz"></param>
        /// <param name="NetWorkYYbm">网系权限</param>
        /// <returns></returns>
        public DataSet GetFaultHistory(string strNode, string starttime, string endtime, string sswx, string gzlx, string gzlxtj, string ssqy, string zrjd, string cxsj, string qztj, string qz, string NetWorkYYbm)
        {

            string sql = string.Empty;

            sql = "select rownum,t.* from ";
            sql += "(select * from ALLNET_FAULT_HISTORY where ";
            sql += strNode;
            if (starttime != "")
            {
                sql += "and endtime>=to_date(\'" + starttime + " 00:00:00\',\'yyyy-mm-dd hh24:mi:ss\') ";
            }
            if (endtime != "")
            {
                sql += "and starttime<=to_date(\'" + endtime + " 23:59:59\',\'yyyy-mm-dd hh24:mi:ss\') ";
            }
            if (sswx != "")
            {
                sql += "and networkid=\'" + sswx + "\' ";
            }

            //如果网系类型不为空的话
            if (gzlx != "")
            {
                //gzlxtj 运算符号
                switch (gzlxtj)
                {
                    case "bd":
                        gzlxtj = "<>";
                        break;
                    case "deng":
                        gzlxtj = "=";
                        break;
                }
                sql += "and OBJECTCLASS " + gzlxtj + "\'" + gzlx + "\' ";
            }
            if (ssqy != "")
            {
                sql += "and nodeid like \'%" + ssqy + "%\' ";
            }
            if (zrjd != "")
            {
                sql += "and maintenanceunit like \'%" + zrjd + "%\' "; 
            }
            if (cxsj != "")
            {
                sql += "and TIMES >= " + cxsj + " ";
            }
            if (qztj != "" && qz != "")
            {
                switch (qztj)
                {
                    case "bd":
                        qztj = "<>";
                        break;
                    case "da":
                        qztj = ">";
                        break;
                    case "xiao":
                        qztj = "<";
                        break;
                    case "deng":
                        qztj = "=";
                        break;
                }
                sql += "and WORKTYPE " + qztj + "\'" + qz + "\' ";
            }
            
            //网系限制
            sql += "and NETWORKID in (" + NetWorkYYbm + ") ";
            sql += "order by starttime asc) t";
            return OracleHelper.Query(sql);
        }

        /// <summary>
        /// 修改权值
        /// </summary>
        /// <param name="tablename">表明</param>
        /// <param name="qzcolum">列</param>
        /// <param name="newqz">新权值</param>
        /// <param name="dealpersonColum"></param>
        /// <param name="userName">用户名称</param>
        /// <param name="faultnoColum">故障列</param>
        /// <param name="faultid">故障id</param>
        /// <returns></returns>
        public int UpdateWorkType(string tablename, string qzcolum, string newqz, string dealpersonColum, string userName, string faultnoColum, string faultid)
        {
            string sql = "update " + tablename + " ";
            sql += "set " + qzcolum + "='" + newqz + "',";
            sql += dealpersonColum + "='" + userName + "，" + DateTime.Now.Month.ToString() + "月" + DateTime.Now.Day.ToString() + "日修改。' ";
            sql += "where " + faultnoColum + "='" + faultid + "'";

            return OracleHelper.ExecuteSql(sql);
        }


        /// <summary>
        /// 存储登陆用户对哪些网有权限访问
        /// </summary>
        public DataSet SaveNetWork(string usergroupisn)
        {
            StringBuilder strSql = new StringBuilder();


            strSql.Append("select * from sec_network sn where sn.fullname in  ");
            strSql.Append("(select operatename from sec_operate t where t.operateid in  ");
            strSql.Append("(select su.operateid  from sec_usergroup_operate su  ");
            strSql.Append("where su.usergroupisn in (" + usergroupisn + ") and length(su.operateid)=2 and su.enabled=1))  ");


            return OracleHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 绑定区域
        /// </summary>
        /// <param name="nodeid">节点id</param>
        /// <returns></returns>
        public OracleDataReader GetSsqy(string nodeid)
        {
            StringBuilder strSql = new StringBuilder();
            if (nodeid == "9701")
            {
                strSql.Append("select * from sec_node where nodeid like '97__' order by nodeid");
            }
            else if (nodeid.ToString().Length >= 6)
            {
                strSql.Append("select * from sec_node where nodeid like '" + nodeid.Substring(0, nodeid.ToString().Length - 2) + "__' order by nodeid");
            }

            return OracleHelper.ExecuteReader(strSql.ToString());

        }

        /// <summary>
        /// 按照网系得到故障类型
        /// </summary>
        public DataSet GetFaultByNet(string netWorkID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from allnet_faulttype where networkid='" + netWorkID + "' and enable='Y' order by faulttype_id ");
            return OracleHelper.Query(strSql.ToString());
        }
      
        
        /// <summary>
        /// 查询日记记录
        /// </summary>
        public DataSet GetDiary(string seltype, string diary, string unitisn)
        {
            StringBuilder strSql = new StringBuilder();

            //不为空或null
            if (!string.IsNullOrEmpty(seltype))
            {
                strSql.Append("select rownum,t.* from  ");
                strSql.Append("(select rownum,d.id,d.tbdw,s.unitname,d.sj,d.zby,d.ywlx,d.nr,d.clzt,d.sbzt from duty_rijirec d,sec_unit s  ");
                strSql.Append("where s.unitisn=d.tbdw and rjid =\'" + diary + "\'  ");
                strSql.Append("order by d.sj desc) t  ");
            }
            else
            {
                strSql.Append("select rownum,t.* from  ");
                strSql.Append("(select rownum,d.id,d.tbdw,s.unitname,d.sj,d.zby,d.ywlx,d.nr,d.clzt,d.sbzt from duty_rijirec d,sec_unit s  ");
                strSql.Append("where s.unitisn=d.tbdw and rjid in  ");
                strSql.Append("(select rjid from duty_riji where zbdw='" + unitisn + "' and jiaobantime is null)  ");
                strSql.Append("order by d.sj desc) t  ");
            }

            return OracleHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 查询交班日记
        /// </summary>
        public DataSet GetFirstDiary(string unitisn)
        {
            StringBuilder strSql = new StringBuilder();


            strSql.Append("select * from duty_riji where zbdw='" + unitisn + "' and jiaobantime is null  ");


            return OracleHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 添加日记
        /// </summary>
        public int AddFirstDiary(string random, string unitisn, string userisn)
        {
            StringBuilder strSql = new StringBuilder();


            strSql.Append("insert into duty_riji (rjid,zbdw,zby,jiebantime) values (   ");
            strSql.Append("'" + random + "',  ");
            strSql.Append("'" + unitisn + "',  ");
            strSql.Append("'" + userisn + "', ");
            strSql.Append(" sysdate)  ");


            return OracleHelper.ExecuteSql(strSql.ToString());
        }

        /// <summary>
        /// 得到用户名称
        /// </summary>
        public OracleDataReader GetZBY(string zby)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select username from sec_userinfo where userisn = '" + zby + "'  ");

            return OracleHelper.ExecuteReader(strSql.ToString());
        }


        /// <summary>
        /// 得到值班日记
        /// </summary>
        public OracleDataReader GetSBZTchuli(string rjrecid)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select * from duty_rijirec where id='" + rjrecid + "'  ");

            return OracleHelper.ExecuteReader(strSql.ToString());
        }


        /// <summary>
        /// 判断是否存在
        /// </summary>
        public bool ValidhaveUpunit(string unitisn)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select * from duty_unitupdown where downunitid='" + unitisn + "'  ");

            OracleDataReader dr = OracleHelper.ExecuteReader(strSql.ToString());
            if (dr.Read())
            {
                return true;
            }
            else
            {
                return false;
            }

            dr.Close();
        }


        /// <summary>
        /// 查询字典
        /// </summary>
        public OracleDataReader GetYWLXchuli(string ywlx)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select * from com_yybm where yybm='" + ywlx + "'  ");

            return OracleHelper.ExecuteReader(strSql.ToString());

        }


        /// <summary>
        /// 查询当前值班时间
        /// </summary>
        public OracleDataReader GetClqk(string unitisn)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select * from duty_riji where zbdw='" + unitisn + "' and jiaobantime is null and rownum=1 order by jiebantime desc  ");

            return OracleHelper.ExecuteReader(strSql.ToString());


        }

        /// <summary>
        /// 查询本班次的处理情况
        /// </summary>
        public OracleDataReader GetClqk1(string rjrecid, string rjid)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select * from duty_clqk ");
            strSql.Append("where rjrecid='" + rjrecid + "'  ");
            strSql.Append("and rjid='" + rjid + "'  ");

            return OracleHelper.ExecuteReader(strSql.ToString());

        }

        /// <summary>
        /// 查询
        /// </summary>
        public OracleDataReader GetZbdw(string unitisn)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select z.*,n.nodename from duty_zbdw z,sec_node n where z.unitid='" +

unitisn + "'  ");
            strSql.Append("and z.zbnodeid=n.nodeid order by kssj  ");

            return OracleHelper.ExecuteReader(strSql.ToString());

        }
        

        /// <summary>
        /// 创建随机数字
        /// </summary>
        public OracleDataReader GetIDRandom()
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select to_char(sysdate,'yyyymmddhh24miss') as id from dual  ");

            return OracleHelper.ExecuteReader(strSql.ToString());


        }

        /// <summary>
        /// 得到日记id
        /// </summary>
        public OracleDataReader GetDirayid(string unitisn)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select * from duty_riji where zbdw='" + unitisn + "' and jiaobantime is null and rownum=1 order by jiebantime desc  ");

            return OracleHelper.ExecuteReader(strSql.ToString());

        }

        /// <summary>
        /// 得到信息
        /// </summary>
        public OracleDataReader GetChaKanRjSB(string rjrecid)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select d.sj,d.zby,d.ywlx,d.nr,s.unitname,s.unitisn,u.username from duty_rijirec d,sec_unit s,sec_userinfo u  ");
            strSql.Append("where d.id='" + rjrecid + "'  ");
            strSql.Append("and d.zby=u.userisn  ");
            strSql.Append("and d.tbdw=s.unitisn  ");

            return OracleHelper.ExecuteReader(strSql.ToString());

        }

        /// <summary>
        /// 得到信息
        /// </summary>
        public DataSet GetsetupYwlx()
        {
            StringBuilder strSql = new StringBuilder();


            strSql.Append("select * from com_yybm where yybm like '90__' and yybm <> '9099'  ");


            return OracleHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 得到信息
        /// </summary>
        public OracleDataReader GetLoadRj(string rjrecid)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select d.sj,d.zby,d.ywlx,d.nr,d.clzt,d.rjrecordid,s.unitname,s.unitisn,u.username from duty_rijirec d,sec_unit s,sec_userinfo u  ");
            strSql.Append("where d.id='" + rjrecid + "'  ");
            strSql.Append("and d.zby=u.userisn  ");
            strSql.Append("and d.tbdw=s.unitisn  ");

            return OracleHelper.ExecuteReader(strSql.ToString());

        }

        /// <summary>
        /// 得到单位信息
        /// </summary>
        public DataSet GetListsjdw(string unitisn)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select unitisn,unitname from sec_unit where unitisn in  ");
            strSql.Append("(select upunitid from duty_unitupdown where downunitid='" + unitisn + "') ");

            return OracleHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 得到用户名称
        /// </summary>
        public OracleDataReader GetUsrName(string userisn)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select * from sec_userinfo where userisn='" + userisn + "' ");

            return OracleHelper.ExecuteReader(strSql.ToString());

        }

        /// <summary>
        /// 查出上级的值班日记
        /// </summary>
        public OracleDataReader GetShangBaoDuty(string cbk)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select * from duty_riji where zbdw='" + cbk + "' and jiaobantime is null and rownum=1 order by jiebantime desc   ");

            return OracleHelper.ExecuteReader(strSql.ToString());

        }

        /// <summary>
        /// 给上级插入日记 这里事物失败会回滚
        /// </summary>
        public int AddShangBaoDuty(string random, string sjrjid, string unitisn, string userisn, string ddlYwlx, string txtNr, string rjrecid)
        {
            List<string> listSql = new List<string>();



            listSql.Add("insert into duty_rijirec (id,rjid,ckzt,tbdw,sj,zby,ywlx,nr,clzt,sbzt,rjrecordid) values  ");
            listSql.Add("('" + random + "','" + sjrjid + "','" + unitisn + "',to_date('" + DateTime.Now.ToString() + "','yyyy-mm-dd hh24:mi:ss'),'" + userisn + "','" + ddlYwlx + "','" + txtNr + "','" + rjrecid + "')   ");

            //修改自己的日记状态
            listSql.Add("update duty_rijirec  ");
            listSql.Add("set sbzt='已上报' where id='" + rjrecid + "'  ");

            return OracleHelper.ExecuteSqlTran(listSql);

        }


        /// <summary>
        /// 修改信息 
        /// </summary>
        public int UpdateDuty(string inputYhDate, string rjrecid)
        {
            StringBuilder strSql = new StringBuilder();

            //修改
            strSql.Append("update duty_rijirec set yhdate=to_date('" + inputYhDate + " 00:00:01','yyyy-mm-dd hh24:mi:ss')  where id='" + rjrecid + "'   ");



            return OracleHelper.ExecuteSql(strSql.ToString());


        }

        /// <summary>
        /// 修改信息 
        /// </summary>
        public int UpdateDuty(string rjrecid, string ddlYwlx, string TxtNr, string ddlClzt)
        {
            StringBuilder strSql = new StringBuilder();


            //修改
            strSql.Append("update duty_rijirec set ywlx='" + ddlYwlx + "', ");
            strSql.Append("nr='" + TxtNr + "',  ");
            strSql.Append("clzt='" + ddlClzt + "',  ");
            strSql.Append("rjrecordid='" + rjrecid + "' where id='" + rjrecid + "'  ");

            return OracleHelper.ExecuteSql(strSql.ToString());


        }

        /// <summary>
        /// 查询当前值班时间
        /// </summary>
        public OracleDataReader GetDelclqk(string unitisn)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select * from duty_riji where zbdw='" + unitisn + "' and jiaobantime is null and rownum=1 order by jiebantime desc   ");

            return OracleHelper.ExecuteReader(strSql.ToString());

        }

        /// <summary>
        /// 删除本班次的处理情况
        /// </summary>
        public int Delclqk(string rjrecid, string rjid)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("delete from duty_clqk ");
            strSql.Append("where rjrecid='" + rjrecid + "' ");
            strSql.Append("and rjid='" + rjid + "'  ");


            return OracleHelper.ExecuteSql(strSql.ToString());


        }

        /// <summary>
        /// 插入本班次的处理情况
        /// </summary>
        public int AddClqk(string rjid, string rjrecid, string txtclqk)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("insert into duty_clqk(rjid,rjrecid,clnr) values(  ");
            strSql.Append("'" + rjid + "','" + rjrecid + "','" + txtclqk + "')   ");


            return OracleHelper.ExecuteSql(strSql.ToString());


        }

        /// <summary>
        /// 插入日记记录
        /// </summary>
        public int AddDuty(string rjrecid, string getRjId, string unitisn, string userisn, string ddlYwlx, string TxtNr, string ddlClzt)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("insert into duty_rijirec (id,rjid,tbdw,sj,zby,ywlx,nr,clzt,sbzt) values ( ");

            strSql.Append("'" + rjrecid + "','" + getRjId + "','" + unitisn + "',to_date('" + DateTime.Now.ToString() + "','yyyy-mm-dd hh24:mi:ss'), '" + userisn + "','" + ddlYwlx + "','" + TxtNr + "','" + ddlClzt + "','未上报')   ");

            return OracleHelper.ExecuteSql(strSql.ToString());

        }

        /// <summary>
        /// 延后处理
        /// </summary>
        public int UpdateYanhou(string inputYhDate, string rjrecid)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("update duty_rijirec set yhdate=to_date('" + inputYhDate + " 00:00:01','yyyy-mm-dd hh24:mi:ss')  where id = '" + rjrecid + "'   ");


            return OracleHelper.ExecuteSql(strSql.ToString());

        }

        /// <summary>
        /// 删除日志记录
        /// </summary>
        public int DelRizhi(string rjrecid)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("delete from duty_rijirec  where id = '" + rjrecid + "'  ");


            return OracleHelper.ExecuteSql(strSql.ToString());

        }

        /// <summary>
        /// 获取历史的处理情况
        /// </summary>
        public DataSet GetLsclqk(string rjrecordid)
        {
            StringBuilder strSql = new StringBuilder();
            //历史各条处理意见
            strSql.Append("select c.clnr,u.unitname,r.zby,r.jiebantime from duty_clqk c,duty_riji r,sec_unit u  ");
            strSql.Append("where c.rjrecid='" + rjrecordid + "' ");
            strSql.Append("and c.rjid=r.rjid  ");
            strSql.Append("and r.zbdw=u.unitisn  ");
            strSql.Append("order by r.jiebantime  ");

            return OracleHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 交班员
        /// </summary>
        public OracleDataReader Getltr(string sczby)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select * from sec_userinfo where userisn='" + sczby + "'  ");

            return OracleHelper.ExecuteReader(strSql.ToString());

        }

        /// <summary>
        /// 交班员1
        /// </summary>
        public DataSet Getltr1(string sczby)
        {
            StringBuilder strSql = new StringBuilder();
            //历史各条处理意见
            strSql.Append("select * from duty_rijirec where rjid in  ");
            strSql.Append("(select rjid from duty_riji where zby='" + sczby + "' and jiaobantime is null) ");

            return OracleHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 交班员2
        /// </summary>
        public DataSet Getltr2(string unitisn)
        {
            StringBuilder strSql = new StringBuilder();
            //历史各条处理意见
            strSql.Append("select * from sec_userinfo where unitisn='" + unitisn + "' order by sort  ");

            return OracleHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 得到用户信息
        /// </summary>
        public OracleDataReader GetUserInfo(string userisn)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select distinct userisn,username,unitisn,unitname,Password,IPSection FROM sec_userinfo WHERE userisn='" + userisn + "'  ");

            return OracleHelper.ExecuteReader(strSql.ToString());

        }

        /// <summary>
        /// 得到单位名称
        /// </summary>
        public OracleDataReader GetUnitInfo(string unitisn)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select s.*,n.nodelevel,n.nodetype from sec_unit s,sec_node n  ");
            strSql.Append("where s.unitisn='" + unitisn + "'  ");
            strSql.Append("and s.upnodeid=n.nodeid  ");

            return OracleHelper.ExecuteReader(strSql.ToString());

        }

        /// <summary>
        /// 查询改用户是否在值班sec_wactnman表里
        /// </summary>
        public OracleDataReader ExistWatchman(string userisn)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select * from sec_watchman where userisn='" + userisn + "'  ");


            return OracleHelper.ExecuteReader(strSql.ToString());

        }

        /// <summary>
        /// 查询值班sec_wactnman表里 值班人员
        /// </summary>
        public DataSet GetWatchman(string unitIsn)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select * from sec_watchman where unitisn='" + unitIsn + "' and iswatchman='1'  ");

            return OracleHelper.Query(strSql.ToString());

        }

        

        /// <summary>
        /// 更新sec_wactnman表的信息
        /// </summary>
        public int UpdateWatchman(string unitisn, string userisn)
        {
            List<string> listSql = new List<string>();

            listSql.Add("UPDATE sec_watchman SET isWatchMan = 0 WHERE UnitISN ='" + unitisn + "'  ");
            listSql.Add("UPDATE sec_watchman SET LastWatchTime =sysdate,isWatchMan = 1 WHERE UserISN ='" + userisn + "'   ");

            return OracleHelper.ExecuteSqlTran(listSql);


        }

        /// <summary>
        /// 更新sec_wactnman表的最后登录时间
        /// </summary>
        public int UpdateWatchmanTime(string userisn)
        {
          
            string strSql = "";
            strSql = "UPDATE sec_watchman SET LastWatchTime =sysdate WHERE UserISN ='" + userisn + "' ";
            
            return OracleHelper.ExecuteSql(strSql);


        }

        /// <summary>
        /// 添加sec_wactnman表的信息
        /// </summary>
        public int AddWatchmanInfo(string unitisn, string userisn, string userName)
        {
          
            string strSql = "";
            strSql = "INSERT into sec_watchman (USERISN,UNITISN,USERNAME,LASTWATCHTIME,ISWATCHMAN) values ('" + userisn + "','" + unitisn + "','" + userName + "',sysdate,1   ";
            
            return OracleHelper.ExecuteSql(strSql);


        }



        /// <summary>
        /// 更新sec_wactnman表的信息
        /// </summary>
        public int UpdateWatchman(string unitisn, string userisn, string userName)
        {
            List<string> listSql = new List<string>();

            listSql.Add("UPDATE sec_watchman SET isWatchMan = 0 WHERE UnitISN ='" + unitisn + "'  ");
            listSql.Add("INSERT into sec_watchman (USERISN,UNITISN,USERNAME,LASTWATCHTIME,ISWATCHMAN) values ('" + userisn + "','" + unitisn + "','" + userName + "',sysdate,1   ");

            return OracleHelper.ExecuteSqlTran(listSql);


        }

        /// <summary>
        /// 更新值班员登录必须强行交接班
        /// </summary>
        public int UpdateSaveJb(string userisn, string inputJbDay, string ddlHour, string sczby, string unitisn, string rjid, string ddlTq, string txtWdmin, string txtWdmax)
        {
            List<string> listSql = new List<string>();

            //删除自己的未交班的日记
            listSql.Add("delete from duty_riji where zby='" + userisn + "' and jiaobantime is null  ");

            //更新上个值班员的交班时间
            listSql.Add("update duty_riji set jiaobantime=to_date('" + inputJbDay + " " + ddlHour + ":00:00','yyyy-mm-dd hh24:mi:ss')  where zby='" + sczby + "' and zbdw='" + unitisn + "'  and jiaobantime is null and rownum=1  ");

            //插入自己的新的值班日记
            listSql.Add("insert into duty_riji(rjid,zbdw,zby,tq,jiebantime,wdmin,wdmax) values ('" + rjid + "','" + unitisn + "','" + userisn + "','" + ddlTq + "',to_date('" + inputJbDay + " " + ddlHour + ":00:00','yyyy-mm-dd hh24:mi:ss'),'" + txtWdmin + "','" + txtWdmax + "')   ");

            return OracleHelper.ExecuteSqlTran(listSql);

        }

        /// <summary>
        /// 查询上个值班员的接班时间
        /// </summary>
        public OracleDataReader GetJieBanTime(string sczby)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select jiebantime from duty_riji where zby='" + sczby + "' and rownum=1 order by jiaobantime desc  ");


            return OracleHelper.ExecuteReader(strSql.ToString());

        }

        /// <summary>
        /// 查询上个值班员的交班时间
        /// </summary>
        public OracleDataReader GetJiaoBanTime(string sczby)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select jiaobantime from duty_riji where zby='" + sczby + "' and rownum=1 order by jiaobantime desc  ");


            return OracleHelper.ExecuteReader(strSql.ToString());

        }

        /// <summary>
        /// 更新上个值班员的交班时间
        /// </summary>
        public int UpdateJieBanTime(string inputJbDay, string ddlHour, string sczby, string unitisn, string jiaobantime)
        {
            StringBuilder strSql = new StringBuilder();

            //更新上个值班员的交班时间
            strSql.Append("update duty_riji set jiaobantime=to_date('" + inputJbDay + " " + ddlHour + ":00:00','yyyy-mm-dd hh24:mi:ss')  where zby='" + sczby + "' and zbdw='" + unitisn + "' and jiaobantime=to_date('" + jiaobantime + "','yyyy-mm-dd hh24:mi:ss')  ");


            return OracleHelper.ExecuteSql(strSql.ToString());

        }

        /// <summary>
        /// 查询本次值班日记
        /// </summary>
        public OracleDataReader GetWatchDiary(string userisn)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select * from duty_riji where zby='" + userisn + "' and jiaobantime is null and rownum=1 ");


            return OracleHelper.ExecuteReader(strSql.ToString());

        }

        /// <summary>
        /// 更新自己的接班时间
        /// </summary>
        public int UpdateMyWatchTime(string txtWdmin, string txtWdmax, string inputJbDay, string ddlHour, string rjid, string ddlTq)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("update duty_riji set  wdmin='" + txtWdmin + "',wdmax='" + txtWdmax + "',tq='" + ddlTq + "',jiebantime=to_date('" + inputJbDay + " " + ddlHour + ":00:00\',\'yyyy-mm-dd hh24:mi:ss\')   ");
            strSql.Append("where rjid=\'" + rjid + "\'  ");


            return OracleHelper.ExecuteSql(strSql.ToString());


        }

        /// <summary>
        /// 更新上个值班员的交班时间
        /// </summary>
        public int UpdateUpWatchTime(string ddljbr, string inputJbDay, string ddlHour, string sczby, string unitisn, string rjid, string ddlTq, string txtWdmin, string txtWdmax)
        {
            List<string> listSql = new List<string>();

            //更新上个值班员的交班时间
            listSql.Add("update duty_riji set jiaobantime=to_date('" + inputJbDay + " " + ddlHour + ":00:00','yyyy-mm-dd hh24:mi:ss')  where zby='" + sczby + "' and zbdw='" + unitisn + "'  and jiaobantime is null  ");

            //插入自己的新的值班日记
            listSql.Add("insert into duty_riji(rjid,zbdw,zby,tq,jiebantime,wdmin,wdmax) values ('" + rjid + "','" + unitisn + "','" + ddljbr + "','" + ddlTq + "',to_date('" + inputJbDay + " " + ddlHour + ":00:00','yyyy-mm-dd hh24:mi:ss'),'" + txtWdmin + "','" + txtWdmax + "')   ");

            return OracleHelper.ExecuteSqlTran(listSql);
            
        }

        /// <summary>
        /// 更新值班排班
        /// </summary>
        public int UpdateZbPb(string jbrusrid, string jbrusrname, string unitisn)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("update duty_arrange set userid='" + jbrusrid + "' , username='" + jbrusrname + "'  where unitisn='" + unitisn + "'  ");
            strSql.Append("and dutyday=to_date('" + DateTime.Now.Date + "','yyyy-mm-dd hh24:mi:ss') ");


            return OracleHelper.ExecuteSql(strSql.ToString());


        }

        /// <summary>
        /// 添加值班排班
        /// </summary>
        public int AddZbPb(string unitisn,string userisn,string userName)
        {
            StringBuilder strSql = new StringBuilder();
            
            strSql.Append("insert into duty_arrange(dutyday,unitisn,userid,username,dutytype,remark,isedit) values(to_date('" + DateTime.Now.Date + "','yyyy-mm-dd hh24:mi:ss'),'" + unitisn + "','" + userisn + "','" + userName + "','节假日','平常班','0') ");
            
            return OracleHelper.ExecuteSql(strSql.ToString());

        }


        /// <summary>
        /// 查询上一班待处理的日记
        /// </summary>
        public DataSet GetWaitDiary(string unitisn)
        {
            StringBuilder strSql = new StringBuilder();
            //历史各条处理意见
            strSql.Append("select * from duty_rijirec where tbdw='" + unitisn + "' and clzt='待处理' order by sj desc  ");

            return OracleHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 上班待处理变为交下一班
        /// </summary>
        public int UpdateDownOver(string unitisn)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("update duty_rijirec set clzt='交下一班' where tbdw='" + unitisn + "' and clzt='待处理'  ");
            return OracleHelper.ExecuteSql(strSql.ToString());

        }

        /// <summary>
        /// 上班待处理变为本班待处理
        /// </summary>
        public int AddDownOver(string random, string rjid, string unitisn, string inputJbDay, string ddlHour, string zby, string ywlx, string nr, string clzt, string rjrecordid)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("insert into duty_rijirec(id,rjid,tbdw,sj,zby,ywlx,nr,clzt,sbzt,rjrecordid) values  ");
            strSql.Append("('" + random + "','" + rjid + "','" + unitisn + "',to_date('" + inputJbDay + " " + ddlHour + ":00:00','yyyy-mm-dd hh24:mi:ss'),'" + zby + "','" + ywlx + "','" + nr + "','" + clzt + "','" + rjrecordid + "') ");
            return OracleHelper.ExecuteSql(strSql.ToString());

        }

        /// <summary>
        /// 查询交班时间时的用户
        /// </summary>
        public DataSet GetRiji(string oneday, string unitisn)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select d.*,s.username from duty_riji d,sec_userinfo_new s  ");
            strSql.Append("where d.jiebantime>=to_date('" + oneday + " 00:00:00','yyyy-mm-dd hh24:mi:ss') and d.jiebantime<to_date('" + oneday + " 23:59:59','yyyy-mm-dd hh24:mi:ss')  ");
            strSql.Append("and d.zby=s.userisn  and zbdw='" + unitisn + "'  ");
            strSql.Append("order by d.jiebantime  ");

            return OracleHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 统计日志
        /// </summary>
        public OracleDataReader GetRjrecCount(string rjid)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select count(*) from duty_rijirec where rjid='" + rjid + "'  ");


            return OracleHelper.ExecuteReader(strSql.ToString());

        }

        /// <summary>
        /// 查询日志记录
        /// </summary>
        public DataSet GetDiaryRecord(string unitisn, string zby, string inputStartTime, string inputEndTime, string ywlx)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select rownum,t.* from  ");
            strSql.Append("(select rownum,d.id,d.tbdw,s.unitname,d.sj,d.zby,d.ywlx,d.nr,d.clzt,d.sbzt from duty_rijirec d,sec_unit s  where s.unitisn=d.tbdw and d.rjid in  ");
            strSql.Append("(select rjid from duty_riji where zbdw='" + unitisn + "'  ");

            if (zby != "")
            {
                strSql.Append("and zby'" + zby + "'  ");
            }

            strSql.Append(")");
            strSql.Append("and sj>=to_date('" + inputStartTime + " 00:00:00','yyyy-mm-dd hh24:mi:ss')  ");

            strSql.Append("and sj<to_date('" + inputEndTime + " 23:59:59','yyyy-mm-dd hh24:mi:ss')  ");

            if (ywlx != "")
            {
                strSql.Append("and ywlx='" + ywlx + "' ");
            }

            strSql.Append(" order by d.sj) t ");


            return OracleHelper.Query(strSql.ToString()); 
        }

        /// <summary>
        /// 查询zby
        /// </summary>
        public DataSet GetSetupZby(string unitisn)
        {
            StringBuilder strSql = new StringBuilder();

            //strSql.Append("select distinct zby from duty_riji where zbdw='" + unitisn + "'  ");
            strSql.Append("select DISTINCT USERISN as ZBY from DUTY_INFO ");
            return OracleHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 值班要点
        /// </summary>
        public DataSet GetSetUpZbyd(string unitisn)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select * from duty_zbyd where ssdw='" + unitisn + "' and isshow='Y' order by numorder  ");

            return OracleHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 查询全部值班要点
        /// </summary>
        public DataSet GetAllZbyd(string unitisn)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select * from duty_zbyd where ssdw='" + unitisn + "'  order by numorder  ");

            return OracleHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 公告
        /// </summary>
        public DataSet GetBulletin(string unitisn)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select afficheid as afficheid,instr(commitedunitid,'" + unitisn + "') as isAccepted,  ");
            strSql.Append("isattachment as isattachment,substr(fromsysunitname,1,10)||' ...' as tosysunit,  ");
            strSql.Append("sender as name,substr(affichetitle,1,20)||' ...' as affichetitle,fromsysunitname,  ");
            strSql.Append("starttime as stime,endtime as etime,'affair_affiche' as tabname  ");
            strSql.Append("from affair_affiche where (starttime<=sysdate or starttime is null)   ");
            strSql.Append("and (endtime>sysdate or endtime is null) and tosysunitid like '%" + unitisn + "%'  ");
            strSql.Append("order by stime desc  ");

            return OracleHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 人员动态
        /// </summary>
        public DataSet GetPersonDynamic(string unitisn)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select * from sec_userinfo where unitisn='" + unitisn + "' order by sort  ");

            return OracleHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 人员动态筛选
        /// </summary>
        public DataSet GetDvPersonDynamic(string unitisn)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select id,ssdw,userisn,nr,shengxiaosj,shixiaosj from duty_rydt where ssdw='" + unitisn + "'");

            return OracleHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 查询单位内人员动态
        /// </summary>
        public DataSet GetEditPersonDynamic(string unitisn)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select d.id,s.userisn,s.username,d.shengxiaosj,d.shixiaosj,d.nr from duty_rydt d, sec_userinfo s  where d.ssdw='" + unitisn + "' and d.userisn=s.userisn order by s.sort  ");

            return OracleHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 修改人员动态
        /// </summary>
        public int UpdatePersonDynamic(string id, string ddluser,string inputShengxiao, string inputShiXiao, string txtNr)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("update duty_rydt set userisn = '" + ddluser +"',  ");
            if (inputShengxiao != "")
            {
                strSql.Append("shengxiaosj = to_date('" + inputShengxiao + "','yyyy-mm-dd hh24:mi:ss'),");
            }
            else
            {
                strSql.Append(" '',");
            }

            if (inputShiXiao != "")
            {
                strSql.Append("shixiaosj = to_date('" + inputShiXiao + "','yyyy-mm-dd hh24:mi:ss'),");
            }
            else
            {
                strSql.Append(" '', ");
            }

            strSql.Append("nr = '" + txtNr + "'  ");
            strSql.Append("where id='" + id + "'");

            return OracleHelper.ExecuteSql(strSql.ToString());

        }

        /// <summary>
        /// 删除
        /// </summary>
        public int DelRydt(string id)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("delete from duty_rydt where id='" + id + "'  ");

            return OracleHelper.ExecuteSql(strSql.ToString());

        }


        /// <summary>
        /// 得到人员动态信息
        /// </summary>
        public OracleDataReader GetxgRydt(string id)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select * from duty_rydt where id='" + id + "' ");


            return OracleHelper.ExecuteReader(strSql.ToString());

        }

        /// <summary>
        /// 查询绑定数据
        /// </summary>
        public DataSet GetInitDdl(string unitisn)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select * from sec_userinfo where unitisn='" + unitisn + "' order by sort  ");

            return OracleHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 根据条件删除
        /// </summary>
        public int DelInitDdl(string xgid, string ddluser)
        {
            StringBuilder strSql = new StringBuilder();

            
            strSql.Append("delete from duty_rydt where id='" + xgid + "' and userisn='" + ddluser + "'  ");
           

            return OracleHelper.ExecuteSql(strSql.ToString());

        }


        /// <summary>
        /// 插入信息
        /// </summary>
        public int AddInitDdl(string ddluser, string random, string unitisn, string inputShengxiao,string inputShiXiao,string txtNr)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("insert into duty_rydt (id,ssdw,userisn,shengxiaosj,shixiaosj,nr) values ('" + random + "','" + unitisn + "','" + ddluser + "',  ");

            if (inputShengxiao != "")
            {
                strSql.Append("to_date('" + inputShengxiao + "','yyyy-mm-dd hh24:mi:ss'),");
            }
            else
            {
                strSql.Append(" '',");
            }

            if (inputShiXiao != "")
            {
                strSql.Append("to_date('" + inputShiXiao + "','yyyy-mm-dd hh24:mi:ss'),");
            }
            else
            {
                strSql.Append(" '', ");
            }   

            strSql.Append("'" + txtNr + "')  ");

            return OracleHelper.ExecuteSql(strSql.ToString());

        }

        /// <summary>
        /// 查询本次汇报的畅通率以及上几次存储的图形数据
        /// </summary>
        public DataSet GetGridDataList(string cyljType,string unitisn)
        {
            StringBuilder strSql = new StringBuilder();
            if(cyljType == "cylj")
            {
                strSql.Append("select id,numorder,name,url,isshow from duty_cyljlink order by numorder  ");
            }

            else if(cyljType == "zbyd")
            {
                strSql.Append("select id,numorder,nr,isshow from duty_zbyd where ssdw='" + unitisn + "' order by numorder  ");
            }

            else if(cyljType == "jqgz")
            {
                strSql.Append("select id,ordernum,nr from duty_jqgz where ssdw='" + unitisn + "' order by ordernum ");
            }

            else if(cyljType == "rydt")
            {
                strSql.Append("select d.id,s.username,d.shengxiaosj,d.shixiaosj,d.nr from duty_rydt d,sec_userinfo s  ");
                strSql.Append("where d.ssdw='" + unitisn + "' and d.userisn=s.userisn order by d.shengxiaosj ");
            }
            

            return OracleHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 得到用户名称
        /// </summary>
        public DataSet GetAddUserName(string unitisn)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select username as 姓名 from sec_userinfo where unitisn='" + unitisn + "' order by sort  ");

            return OracleHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 删除值班要点原有数据
        /// </summary>
        public int DelDutyZbyd(string id)
        {
            StringBuilder strSql = new StringBuilder();

            
            strSql.Append("delete from duty_zbyd where id = '" + id + "'  ");
           

            return OracleHelper.ExecuteSql(strSql.ToString());

        }

        /// <summary>
        /// 添加值班要点
        /// </summary>
        public int AddDutyZbyd(string id,int order,string nr,string isShow,string unitisn)
        {
            StringBuilder strSql = new StringBuilder();

            
            strSql.Append("insert into duty_zbyd (ID,NUMORDER,NR,ISSHOW,SSDW) values ('" + id + "','" + order + "','" + nr + "','" + isShow + "','" + unitisn + "')  ");
           

            return OracleHelper.ExecuteSql(strSql.ToString());

        }

        /// <summary>
        /// 修改值班要点
        /// </summary>
        public int UpdateDutyZbyd(string id, int order, string nr, string isShow)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("update duty_zbyd set NUMORDER = '" + order + "',NR = '" + nr + "',ISSHOW = '"+ isShow +"' where ID = '"+ id +"'  ");
            return OracleHelper.ExecuteSql(strSql.ToString());

        }

        /// <summary>
        /// 删除常用链接
        /// </summary>
        /// <param name="id">id</param>
        public int DelCyljlink(string id)
        {
            StringBuilder strSql = new StringBuilder();

            
            strSql.Append("delete from duty_cyljlink where id = '"+ id +"' ");
           

            return OracleHelper.ExecuteSql(strSql.ToString());

        }

        /// <summary>
        /// 添加常用链接
        /// </summary>
        /// <param name="id">id</param>
        /// <param name="order">顺序号</param>
        /// <param name="name">名称</param>
        /// <param name="url">地址</param>
        /// <param name="isShow">是否显示</param>
        /// <returns></returns>
        public int AddCyljlink(string id, int order, string name, string url, string isShow)
        {
            StringBuilder strSql = new StringBuilder();


            strSql.Append("insert into duty_cyljlink (ID,NUMORDER,NAME,URL,ISSHOW) values ('" + id + "','" + order + "','" + name + "','" + url + "','" + isShow + "')  ");
           

            return OracleHelper.ExecuteSql(strSql.ToString());

        }

        /// <summary>
        /// 修改常用链接
        /// </summary>
        /// <param name="id">id</param>
        /// <param name="order">顺序号</param>
        /// <param name="name">名称</param>
        /// <param name="url">地址</param>
        /// <param name="isShow">是否显示</param>
        /// <returns></returns>
        public int UpdateCyljlink(string id, int order, string name, string url,string isShow)
        {
            StringBuilder strSql = new StringBuilder();

            
            strSql.Append("update duty_cyljlink set NUMORDER = '"+ order +"' ,NAME = '"+ name +"',URL = '"+ url +"' ,ISSHOW = '"+ isShow +"' where id = '"+ id +"'  ");


            return OracleHelper.ExecuteSql(strSql.ToString());

        }

        /// <summary>
        /// 得到常用链接
        /// </summary>
        /// <returns></returns>
        public DataSet GetCyljlink()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,numorder,name,url,isshow from duty_cyljlink order by numorder ");

            return OracleHelper.Query(strSql.ToString());
        }

        
        
        /// <summary>
        /// 删除近期工作
        /// </summary>
        public int DelDutyJqgz(string unitisn)
        {
            StringBuilder strSql = new StringBuilder();

            
            strSql.Append("delete from duty_jqgz where ssdw='" + unitisn + "'  ");
           

            return OracleHelper.ExecuteSql(strSql.ToString());

        }

        /// <summary>
        /// 添加近期工作
        /// </summary>
        public int AddDutyJqgz(int i,string cell1,string cell2,string unitisn)
        {
            StringBuilder strSql = new StringBuilder();


            strSql.Append("insert into duty_jqgz (ID,ORDERNUM,NR,SSDW) values ('" + i + "','" + cell1 + "','" + cell2 + "','" + unitisn + "')  ");
           

            return OracleHelper.ExecuteSql(strSql.ToString());

        }

        /// <summary>
        /// 删除人员动态
        /// </summary>
        public int DelDutyRydt(string unitisn)
        {
            StringBuilder strSql = new StringBuilder();

            
            strSql.Append("delete from duty_rydt where ssdw='" + unitisn + "'  ");
           

            return OracleHelper.ExecuteSql(strSql.ToString());

        }

        /// <summary>
        /// 添加人员动态
        /// </summary>
        public int AddDutyRydt(int i,string random,string cell1,string cell4,string unitisn)
        {
            StringBuilder strSql = new StringBuilder();


            strSql.Append("insert into duty_rydt (ID,ssdw,userisn,nr,shengxiaosj,shixiaosj) values ('" + random + "','" + unitisn + "',(select userisn from sec_userinfo where username='" + cell1 + "' and ssdw='" + unitisn + "'),'" + cell4 + "')  ");
           

            return OracleHelper.ExecuteSql(strSql.ToString());

        }

        /// <summary>
        /// 得到下级单位
        /// </summary>
        public DataSet GetUnitupdown(string unitisn)
        {
            OracleParameter[] parames = {
                new OracleParameter("v_unitisn",OracleType.VarChar,20),
                new OracleParameter("v_cursor",OracleType.Cursor)
            };

            parames[0].Value = unitisn;
            parames[1].Direction = ParameterDirection.Output;

            return OracleHelper.RunProcedure("duty_unitupdown_selectbyunit", parames, "ds");

        }

        /// <summary>
        /// 得到下级单位值班员在线情况
        /// </summary>
        /// <param name="unitisn">单位id</param>
        /// <returns></returns>
        public DataSet GetUnitupdownOnline(string unitisn)
        {
            OracleParameter[] parames = {
                new OracleParameter("v_unitisn",OracleType.VarChar,20),
                new OracleParameter("v_cursor",OracleType.Cursor)
            };

            parames[0].Value = unitisn;
            parames[1].Direction = ParameterDirection.Output;

            return OracleHelper.RunProcedure("duty_unitupdown_online", parames, "ds");

        }

        /// <summary>
        /// 得到上级单位单位值班员情况
        /// </summary>
        /// <param name="unitisn">单位id</param>
        /// <param name="isvalid">单位是否为网管中心</param>
        /// <returns></returns>
        public DataSet GetUnitupdownParent(string unitisn,bool isvalid)
        {
            StringBuilder strSql = new StringBuilder();

            if(isvalid)
            {
                strSql.Append("select upunitid,downunitid from duty_unitupdown where downunitid='" + unitisn + "' ");
                strSql.Append("union select '20061206104336470','" + unitisn + "' from dual ");
            }
            else
            {
                strSql.Append("select * from duty_unitupdown where downunitid='" + unitisn + "' ");
            }
           

            return OracleHelper.Query(strSql.ToString());

        }

        
        /// <summary>
        /// 得到故障名称
        /// </summary>
        /// <param name="faultTypeID">故障类型id</param>
        /// <returns></returns>
        public OracleDataReader GetFaultType(string faultTypeID)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select * from allnet_faulttype where faulttype_id='" + faultTypeID + "' ");


            return OracleHelper.ExecuteReader(strSql.ToString());

        }

        /// <summary>
        /// 得到故障启用状态为开启
        /// </summary>
        /// <returns></returns>
        public DataSet GetFaultEnable()
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select * from allnet_faulttype where enable='Y' ");


            return OracleHelper.Query(strSql.ToString());

        }

        /// <summary>
        /// 故障处置意见
        /// </summary>
        /// <returns></returns>
        public DataSet GetFaultTreatment(string netid, string faultid,string tableName)
        {
            StringBuilder strSql = new StringBuilder();

            if (netid == "9001" || netid == "9005" || netid == "9006" || netid == "9007" || netid == "9008")
            {
                strSql.Append("select t.dealno id,s.username clr,s.unitname cldw,t.dealtime clsj,t.dealnote clyj from " + tableName + " t,sec_userinfo s ");
                strSql.Append("where t.faultserialno='" + faultid + "' ");
                strSql.Append("and t.dealperson=s.userisn ");
                strSql.Append("order by clsj");
            }
            else if (netid == "9002")
            {
                strSql.Append("select alarm_serial id,handler clr,department cldw,dealtime clsj,treatmind clyj from " + tableName + " ");
                strSql.Append("where alarm_serial='" + faultid + "' ");
                strSql.Append("order by clsj");
            }
            else if (netid == "9003")
            {
                //@ deal_time ?
                strSql.Append("select alarm_serial id,handler clr,department cldw,deal_time clsj,treatmind clyj from " + tableName + " ");
                strSql.Append("where alarm_serial='" + faultid + "' ");
                strSql.Append("order by clsj");
            }


            return OracleHelper.Query(strSql.ToString());

        }

        /// <summary>
        /// 故障种类,自动网没有种类
        /// </summary>
        /// <param name="netid">网系id</param>
        /// <returns></returns>
        public OracleDataReader GetFaultTypeByNet(string netid)
        {

            StringBuilder strSql = new StringBuilder();

            if (netid == "9001")
            {
                strSql.Append( "select yybm,yymc from com_yybm where yybm like '8201__' order by yybm desc");
            }
            else if (netid == "9002")
            {
                strSql.Append( "select yybm,yymc from com_yybm where yybm like '8202__' order by yybm desc");
            }
            else if (netid == "9003")
            {
                strSql.Append( "select yybm,yymc from com_yybm where yybm like '8203__' order by yybm desc");
            }
            else if (netid == "9005")
            {
                strSql.Append( "select yybm,yymc from com_yybm where yybm like '8205__' order by yybm desc");
            }
            else if (netid == "9006")
            {
                strSql.Append( "select yybm,yymc from com_yybm where yybm like '8206__' order by yybm desc");
            }
            else if (netid == "9007")
            {
                strSql.Append( "select yybm,yymc from com_yybm where yybm like '8207__' order by yybm desc");
            }
            else if (netid == "9008")
            {
                strSql.Append( "select yybm,yymc from com_yybm where yybm like '8208__' order by yybm desc");
            }

            return OracleHelper.ExecuteReader(strSql.ToString());

        }

        /// <summary>
        /// 故障种类的值
        /// </summary>
        /// <param name="netid">网系id</param>
        /// <param name="faulttable">故障表名</param>
        /// <param name="faultid">故障id</param>
        /// <returns></returns>
        public OracleDataReader GetFaultTypeValue(string netid, string faulttable,string faultid)
        {

            StringBuilder strSql = new StringBuilder();

            if (netid == "9001" || netid == "9005" || netid == "9006" || netid == "9007" || netid == "9008")
            {
                strSql.Append( "select faulttype from " + faulttable + " where faultserialno='" + faultid + "' ");
            }
            else if (netid == "9002")
            {
                strSql.Append( "select faulttype from " + faulttable + " where alarm_serial='" + faultid + "' ");
            }
            else if (netid == "9003")
            {
                strSql.Append( "select faulttype from " + faulttable + " where alarm_serial='" + faultid + "' ");
            }		

            return OracleHelper.ExecuteReader(strSql.ToString());

        }

        /// <summary>
        /// 的到故障里的权值
        /// </summary>
        /// <param name="faultid">故障id</param>
        /// <returns></returns>
        public string GetFaultWorkType(string faultid)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select worktype from allnet_t_fault where faultserialno='" + faultid + "' ");

            object obj = OracleHelper.GetSingle(strSql.ToString());

            if (obj == null)
            {
                return string.Empty;
            }
            else
            {
                return obj.ToString();
            }
            

        }

        /// <summary>
        /// 添加故障处理意见
        /// </summary>
        /// <param name="id">重保单位id</param>
        /// <returns></returns>
        public int AddFaultIdea(string netid, string faulttable,string hisfaulttable, string strClyj, string faultid, string notetable,string randomid, string userisn, string nodeid, string unitisn,string userName,string unitName)
        {
            List<string> listSql = new List<string>();
            string sqlup = string.Empty;
            string sqlup1 = string.Empty;
            string sql = string.Empty;
            
            if (netid == "9001" || netid == "9005" || netid == "9007" || netid == "9008")
            {
                //本网故障当前表
                sqlup = "update " + faulttable + " set confirmcause='" + strClyj + "' where faultserialno='" + faultid + "'";
                

                //历史处理意见
                sql = "insert into " + notetable + "(dealno,faultserialno,dealperson,dealnode,dealtime,dealnote,dealunitname) ";
                sql += "values (";
                sql += "'" + randomid + "',";
                sql += "'" + faultid + "',";
                sql += "'" + userisn + "',";
                sql += "'" + nodeid + "',";
                sql += "sysdate,";
                sql += "'" + strClyj + "',";
                sql += "'" + unitisn + "')";
               
            }
            else if (netid == "9006")
            {
                //自动电话网拨打记录表
                sqlup = "update " + faulttable + " set confirmcause='" + strClyj + "' where dialednode|| to_char(dialed_dialbegin,'yyyymmddhh24miss')='" + faultid + "'";
                
                //历史处理意见
                sql = "insert into " + notetable + "(dealno,faultserialno,dealperson,dealnode,dealtime,dealnote,dealunitname) ";
                sql += "values (";
                sql += "'" + randomid + "',";
                sql += "'" + faultid + "',";
                sql += "'" + userisn + "',";
                sql += "'" + nodeid + "',";
                sql += "sysdate,";
                sql += "'" + strClyj + "',";
                sql += "'" + unitisn + "')";
                
            }
            else if (netid == "9002")
            {
                //本网故障当前表
                sqlup = "update " + faulttable + " set dealnote='" + strClyj + "' where alarm_serial='" + faultid + "'";
                
                //本网故障历史表
                sqlup1 = "update " + hisfaulttable + " set dealnote='" + strClyj + "' where alarm_serial='" + faultid + "'";
                
                //历史处理意见
                sql = "insert into " + notetable + "(alarm_serial,treatmind,handler,department,dealtime) ";
                sql += "values (";
                sql += "'" + faultid + "',";
                sql += "'" + strClyj + "',";
                sql += "'" + userName + "',";
                sql += "'" + unitName + "',";
                sql += "sysdate)";
                
            }
            else if (netid == "9003")
            {
                //本网故障当前表
                sql = "update " + faulttable + " set dealnote='" + strClyj + "' where alarm_serial='" + faultid + "'";
                
                //本网故障历史表
                sqlup1 = "update " + hisfaulttable + " set dealnote='" + strClyj + "' where alarm_serial='" + faultid + "'";
                
                //历史处理意见
                sql = "insert into " + notetable + "(id,alarm_serial,treatmind,user_node,handler,department,deal_time) ";
                sql += "values (";
                sql += "'" + randomid + "',";
                sql += "'" + faultid + "',";
                sql += "'" + strClyj + "',";
                sql += "'" + nodeid + "',";
                sql += "'" + userName + "',";
                sql += "'" + unitName + "',";
                sql += "sysdate)";
                
            }

            //添加语句
            listSql.Add(sqlup);
            if (!string.IsNullOrEmpty(sqlup1))
            {
                listSql.Add(sqlup1);
            }
            listSql.Add(sql);



            //综合故障当前临时表
            listSql.Add("update allnet_fault_temp set confirmcause='" + strClyj + "' where faultserialno='" + faultid + "'");
            
            //综合故障当前表
            listSql.Add("update allnet_t_fault set confirmcause='" + strClyj + "' where faultserialno='" + faultid + "'");

            return OracleHelper.ExecuteSqlTran(listSql);

        }

        /// <summary>
        /// 保存权值
        /// </summary>
        /// <returns></returns>
        public int UpdateFaultQz(string faulttable,string qzcolum,string txtQz,string faultserialno,string faultid)
        {
            List<string> listSql = new List<string>();

            listSql.Add("update " + faulttable + " set " + qzcolum + "='" + txtQz + "' where " + faultserialno + "='" + faultid + "' ");
            listSql.Add("update allnet_t_fault set worktype='" + txtQz + "' where faultserialno='" + faultid + "' ");
           
            return OracleHelper.ExecuteSqlTran(listSql);

        }

        /// <summary>
        /// 修改故障类型
        /// </summary>
        /// <param name="netid">网系id</param>
        /// <param name="faulttable">故障表名</param>
        /// <param name="faultType">故障类型</param>
        /// <param name="faultid">故障id</param>
        /// <param name="hisfaulttable">历史故障表</param>
        /// <returns></returns>
        public int UpdateFaultType(string netid, string faulttable, string faultType, string faultid, string hisfaulttable)
        {
            List<string> listSql = new List<string>();
            string sql = string.Empty;
            string sqlup = string.Empty;

            if (netid == "9001" || netid == "9005" || netid == "9007" || netid == "9008")
            {
                //本网故障当前表
                sql = "update " + faulttable + " set faulttype='" + faultType + "' where faultserialno='" + faultid + "'";
               
            }
            else if (netid == "9006")
            {
                //自动电话网拨打记录表
                sql = "update " + faulttable + " set faulttype='" + faultType + "' where dialednode|| to_char(dialed_dialbegin,'yyyymmddhh24miss')='" + faultid + "'";
               
            }
            else if (netid == "9002")
            {
                //本网故障当前表
                sql = "update " + faulttable + " set faulttype='" + faultType + "' where alarm_serial='" + faultid + "'";
                
                //本网故障历史表
                sqlup = "update " + hisfaulttable + " set faulttype='" + faultType + "' where alarm_serial='" + faultid + "'";
                
            }
            else if (netid == "9003")
            {
                //本网故障当前表
                sql = "update " + faulttable + " set faulttype='" + faultType + "' where alarm_serial='" + faultid + "'";
                
                //本网故障历史表
                sqlup = "update " + hisfaulttable + " set faulttype='" + faultType + "' where alarm_serial='" + faultid + "'";
                
            }

            listSql.Add(sql);
            if (!string.IsNullOrEmpty(sqlup))
            {
                listSql.Add(sqlup);
            }

           
            return OracleHelper.ExecuteSqlTran(listSql);

        }

        

        /// <summary>
        /// 得到重保单位数据
        /// </summary>
        /// <param name="unitisn">单位id</param>
        /// <returns></returns>
        public DataSet GetZbdwByUnit(string unitisn)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select z.*,n.nodename from duty_zbdw z,sec_node n where z.unitid='" + unitisn + "' and z.zbnodeid=n.nodeid order by kssj desc ");

            return OracleHelper.Query(strSql.ToString());

        }

        /// <summary>
        /// 删除重保单位数据
        /// </summary>
        /// <param name="id">重保单位id</param>
        /// <returns></returns>
        public int DelZbdw(string id)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("delete from DUTY_ZBDW where id='" + id + "' ");
           
            return OracleHelper.ExecuteSql(strSql.ToString());

        }

        /// <summary>
        /// 得到重保单位节点
        /// </summary>
        /// <param name="unitisn">单位id</param>
        /// <returns></returns>
        public DataSet GetZbdwByNode(string nodeid)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select * from sec_node where nodeid not like '%A%' and nodeid like '" + nodeid.Substring(0, nodeid.Length - 2) + "%' order by nodeid ");

            return OracleHelper.Query(strSql.ToString());

        }

        /// <summary>
        /// 添加重保单位数据
        /// </summary>
        /// <param name="model">重保单位属性集合</param>
        /// <returns></returns>
        public int AddZbdw(Duty_Zbdw model)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("insert into duty_zbdw (id,unitid,zbnodeid,zbnr,kssj,jssj) values ( ");
            strSql.Append("'"+ model.ID +"', ");
            strSql.Append("'"+ model.UNITID +"', ");
            strSql.Append("'"+ model.ZBNODEID +"', ");
            strSql.Append("'"+ model.ZBNR +"', ");
            strSql.Append("to_date('" + model.KSSJ + " 00:00:00','yyyy-mm-dd hh24:mi:ss'), ");
            strSql.Append("to_date('" + model.JSSJ + " 23:59:59','yyyy-mm-dd hh24:mi:ss')) ");
            
            return OracleHelper.ExecuteSql(strSql.ToString());

        }

       


        
    }
       
}
